--末端驿站汇总
insert overwrite table jms_dm.dm_terminal_station_brand_sum_dt partition (dt)
select final_plan_sign_date,  --规划签收日期
         end_agent_code,  --末端代理区code
       max(end_agent_name) as end_agent_name, --末端代理区
         end_franchisee_code, --末端加盟商code
         max(end_franchisee_name) as end_franchisee_name,  --末端加盟商
         final_sign_network_code,  --末端网点code
       max(final_sign_network_name) as final_sign_network_name,  -- 末端网点
         terminal_source,  -- 对接终端来源
       count(1) as need_sign_cnt,  --总单量
       sum(if(deliver_shift=1,1,0)) as deliver_1_cnt,  -- 一派总量
       sum(if(deliver_shift=2,1,0)) as deliver_2_cnt,  --二派总量
        sum(case when is_aging_sign_22 = 1 then 1 else 0 end) as aging_sign_22_count,--时效签收-22点前签收汇总
        sum(case when deliver_shift=1 and  is_aging_sign_22 = 1 then 1 else 0 end) as deliver_one_aging_sign_22_count,--时效签收-22点前签收汇总
        sum(case when deliver_shift=2 and  is_aging_sign_22 = 1 then 1 else 0 end) as deliver_two_aging_sign_22_count,--时效签收-22点前签收汇总
        sum(if(collect_in_time is not null ,1,0)) as collect_in_cnt,  --入库量
        sum(if(collect_out_time is not null ,1,0)) as collect_out_cnt,  --出库量
       sum(case when end_type <> '完结' then 1 else 0 end) as unfinished_count,  --未完结量
      final_plan_sign_date as date_time,  --业务日期
      if(max(deliver_township_name) is not null ,1,0) as is_township,   --是否乡镇
      sum(case when actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00') then 1 else 0 end) as actual_sign_22_count,--实际签收-22点前签收汇总
       final_plan_sign_date as dt  --分区日期
from jms_dm.dm_terminal_sign_detail_new_dt
where dt>=date_sub('{{ execution_date  | cst_ds }}',15)
  and dt<='{{ execution_date  | cst_ds }}'
and terminal_type='驿站'
and final_plan_sign_date='{{ execution_date  | cst_ds }}'
group by final_plan_sign_date,
         end_agent_code,
         end_franchisee_code,
         final_sign_network_code,
         terminal_source
distribute by 1;